﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;
namespace sql表结构对比
{
    partial class Form1
    {
        //excel表格数据导入数据库
        public bool TransferData(string excelFile, string sheetName, string connectionString, string sqltablename, out string error)
        {
            DataSet ds = new DataSet();
            string strCon = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + excelFile + ";Extended Properties='Excel 12.0;HDR=NO;IMEX=1'";
            string t = "";
            try
            {
                ds.Clear();

                using (OleDbConnection conn = new OleDbConnection(strCon))
                {
                    using (OleDbDataAdapter da = new OleDbDataAdapter("Select * from [" + sheetName + "$]", conn))
                    {
                        da.Fill(ds, "xlssheet");
                    }
                }
                if (sqltablename == "")
                {
                    sqltablename = sheetName;
                }
                else
                {

                }
                string strsql = string.Format("if not exists(select * from sysobjects where name = '{0}') create table {0}(", sqltablename);
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {

                    string temp = ds.Tables[0].Rows[0][i].ToString();
                    if (temp == "")
                    {

                        ds.Tables[0].Columns.RemoveAt(i);
                        i--;
                        continue;
                    }
                    else
                    {
                        strsql += string.Format("[{0}] varchar(255),", temp);
                    }
                }
                if(ds.Tables[0].Columns.Count==0){
                    error = "该表并没有内容！请重新选择吧.";
                    return false;
                }
                strsql = strsql.Trim(',') + ")";

                SqlHelper.ExecuteNonQuery(strsql, connectionString, out t);
                ds.Tables[0].Rows.RemoveAt(0);
                using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
                {
                    bcp.BatchSize = 100;
                    bcp.NotifyAfter = 100;
                    bcp.DestinationTableName = sqltablename;
                    bcp.WriteToServer(ds.Tables[0]);
                }
                if (t != "")
                {
                    error = t;
                }
                else
                {
                    error = "执行成功!";
                }

                return true;
            }
            catch (Exception ex)
            {
                if (t != "")
                {
                    error = t + "\r\n" + ex.Message;
                }
                else
                {
                    error = ex.Message;
                }

                return false;
            }
        }
        //获取excel文件的表名
        public List<string> GetSheetNames(string excelfile, out string info)
        {
            try
            {
                List<string> sheetnames = new List<string>();
                string strCon = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + excelfile + ";Extended Properties='Excel 12.0;HDR=NO;IMEX=1'";
                using (OleDbConnection conn = new OleDbConnection(strCon))
                {
                    conn.Open();
                    System.Data.DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        sheetnames.Add(dt.Rows[i][2].ToString().Replace("$", "").Trim());
                    }
                }
                info = "";
                return sheetnames;
            }
            catch (Exception ex)
            {
                info = ex.Message;
                return null;

            }

        }

        //判断当手动在输入框输入值时，是否与加载的内容匹配
        public bool match(ComboBox T)
        {
            if (T.Items.Contains(T.Text))
            {
                return true;
            }
            else
            {
                return false;
            }
        }




    }
}
